﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SnailGame.Utility.DataAccess;
using System.Configuration;
using System.Collections;
using Com.Crc.PoJo.Model.Resource;

namespace Com.Crc.ResManage.DAL.EquipManage
{
    public class BroadBandManagerSubDistrictDAL
    {
        DataBroker broker = new DataBroker();
        /// <summary>
        /// 删除设备，修改状态位为0
        /// </summary>
        /// <param name="wlanList"></param>
        /// <returns></returns>
        public bool DeleteSubDistrictData(ArrayList subDistrictList)
        {
            string id = "";
            for (int i = 0; i < subDistrictList.Count; i++)
            {
                id += subDistrictList[i] + ",";
            }
            id = id.Substring(0, id.Length - 1);
            string strSql = @"UPDATE TT_RESOURCE_SUBDISTRICT SET S_STATE='0' WHERE N_ID IN (" + id + ")";
            return broker.ExecuteForOleDB(strSql, ConfigurationManager.ConnectionStrings["CRCAppConn"].ToString());
        }

        /// <summary>
        /// 修改交换设备信息
        /// </summary>
        /// <param name="data">model实体</param>
        /// <returns></returns>
        public bool UpdateSubDistrictData(SubDistrict data)
        {
            string strSql = @" UPDATE TT_RESOURCE_SUBDISTRICT
                                  SET" +
                                    "N_NO=" + data.N_NO + @",
                                        S_NAME='" + data.S_NAME + @"',
                                        N_COVG_ID=" + data.N_COVG_ID + @",
                                        N_CONTENT_ID=" + data.N_CONTENT_ID + @",
                                        N_PARENT_ID=" + data.N_PARENT_ID + @",
                                        N_BUSNIESSDEPARTMENT=" + data.N_BUSNIESSDEPARTMENT + @",
                                        S_ARRRESS='" + data.S_ARRRESS + @"',
                                        D_START='" + data.D_START.ToString("yyyy-MM-dd HH:mm:ss") + @"',
                                        N_HOURSE_TYPE_ID=" + data.N_HOURSE_TYPE_ID + @",
                                        N_HOURSE_NUM=" + data.N_HOURSE_NUM + @",
                                        N_HOURSE_FAMILY=" + data.N_HOURSE_FAMILY + @",
                                        N_L_BOX_NUM=" + data.N_L_BOX_NUM + @",
                                        N_L_MACHINE_NUM=" + data.N_L_MACHINE_NUM + @",
                                        N_L_INFO=" + data.N_L_INFO + @",
                                        N_A_BOX_NUM=" + data.N_A_BOX_NUM + @",
                                        N_B_BOX_NUM=" + data.N_B_BOX_NUM + @",
                                        N_HL_HC_BOX_NUM=" + data.N_HL_HC_BOX_NUM + @",
                                        N_PON_NUM=" + data.N_PON_NUM + @",
                                        S_ESTATE_CONTACTER='" + data.S_ESTATE_CONTACTER + @"',
                                        S_ESTATE_PHONE='" + data.S_ESTATE_PHONE + @"',
                                        S_MH_TROOPS='" + data.S_MH_TROOPS + @"',
                                        S_MH_MAINPERSON='" + data.S_MH_MAINPERSON + @"',
                                        S_BAK='" + data.S_BAK + @"',
                                        S_STATE='" + data.S_STATE + @"',
                                        D_CREATE='" + data.D_CREATE.ToString("yyyy-MM-dd HH:mm:ss") + @"',
                                        D_MODIFY='" + data.D_MODIFY.ToString("yyyy-MM-dd HH:mm:ss") + @"',
                                    WHERE N_ID=" + data.N_ID;
            return broker.ExecuteForOleDB(strSql, ConfigurationManager.ConnectionStrings["CRCAppConn"].ToString());
        }

        /// <summary>
        /// 新增交换设备信息
        /// </summary>
        /// <param name="strBranchID">分局ID</param>
        /// <param name="strOfficeID">局点ID</param>
        /// <param name="strEquipID">设备ID</param>
        /// <returns></returns>
        public bool AddSubDistrictData(SubDistrict data)
        {
            string strSql = @" INSERT INTO TT_RESOURCE_SUBDISTRICT(N_NO,S_NAME,N_COVG_ID,N_CONTENT_ID,N_PARENT_ID,N_BUSNIESSDEPARTMENT,S_ARRRESS,D_START,N_HOURSE_TYPE_ID,N_HOURSE_NUM,N_HOURSE_FAMILY,N_L_BOX_NUM,N_L_MACHINE_NUM,N_L_INFO,N_A_BOX_NUM,N_B_BOX_NUM,N_HL_HC_BOX_NUM,N_PON_NUM,S_ESTATE_CONTACTER,S_ESTATE_PHONE,S_MH_TROOPS,S_MH_MAINPERSON,S_BAK,S_STATE,D_CREATE,D_MODIFY)
                                    VALUES (" +
                                                data.N_NO + "," +
                                                "'" + data.S_NAME + "'" + "," +
                                                data.N_COVG_ID + "," +
                                                data.N_CONTENT_ID + "," +
                                                data.N_PARENT_ID + "," +
                                                data.N_BUSNIESSDEPARTMENT + "," +
                                                "'" + data.S_ARRRESS + "'" + "," +
                                                "'" + data.D_START.ToString("yyyy-MM-dd HH:mm:ss") + "'" + "," +
                                                data.N_HOURSE_TYPE_ID + "," +
                                                data.N_HOURSE_NUM + "," +
                                                data.N_HOURSE_FAMILY + "," +
                                                data.N_L_BOX_NUM + "," +
                                                data.N_L_MACHINE_NUM + "," +
                                                data.N_L_INFO + "," +
                                                data.N_A_BOX_NUM + "," +
                                                data.N_B_BOX_NUM + "," +
                                                data.N_HL_HC_BOX_NUM + "," +
                                                data.N_PON_NUM + "," +
                                                "'" + data.S_ESTATE_CONTACTER + "'" + "," +
                                                "'" + data.S_ESTATE_PHONE + "'" + "," +
                                                "'" + data.S_MH_TROOPS + "'" + "," +
                                                "'" + data.S_MH_MAINPERSON + "'" + "," +
                                                "'" + data.S_BAK + "'" + "," +
                                                "'" + data.S_STATE + "'" + "," +
                                                "'" + data.D_CREATE.ToString("yyyy-MM-dd HH:mm:ss") + "'" + "," +
                                                "'" + data.D_MODIFY.ToString("yyyy-MM-dd HH:mm:ss") + "'" + ")";
            return broker.ExecuteForOleDB(strSql, ConfigurationManager.ConnectionStrings["CRCAppConn"].ToString());
        }
        /// <summary>
        /// 查询交换设备信息
        /// </summary>
        /// <param name="strBranchID">分局ID</param>
        /// <param name="strOfficeID">局点ID</param>
        /// <param name="strEquipID">设备ID</param>
        /// <returns></returns>
        public System.Data.DataTable QueryALL()
        {
            string strSql = @" SELECT N_ID,N_NO,S_NAME,N_COVG_ID,N_CONTENT_ID,N_PARENT_ID,N_BUSNIESSDEPARTMENT,S_ARRRESS,D_START,N_HOURSE_TYPE_ID,N_HOURSE_NUM,N_HOURSE_FAMILY,N_L_BOX_NUM,N_L_MACHINE_NUM,N_L_INFO,N_A_BOX_NUM,N_B_BOX_NUM,N_HL_HC_BOX_NUM,N_PON_NUM,S_ESTATE_CONTACTER,S_ESTATE_PHONE,S_MH_TROOPS,S_MH_MAINPERSON,S_BAK,S_STATE,D_CREATE,D_MODIFY FROM TT_RESOURCE_SUBDISTRICT WHERE S_STATE='1'  ";
            return broker.ExecuteQueryForOleDB(strSql, ConfigurationManager.ConnectionStrings["CRCAppConn"].ToString());
        }

        public System.Data.DataTable queryAllSubdistictUnitInfo()
        {
            string strSql = @"select b.N_ID,b.S_BUILDING+'栋'+b.S_UNIT+'单元'+b.S_ROOM+'室号'  as s_name from dbo.TT_RESOURCE_SUBDISTRICT a,dbo.TT_SUBDISTRICT_COVER b where a.n_id = b.N_SUBDISTRICT_ID and a.S_STATE='1' and b.S_STATE='1' ";
            return broker.ExecuteQueryForOleDB(strSql, ConfigurationManager.ConnectionStrings["CRCAppConn"].ToString());
        }

        public System.Data.DataTable queryAllSubdistictInfo()
        {
            string strSql = @"select isNull(cast(a.N_AREA_ID as varchar),'-1')+'|'+isNull(cast(a.n_id as varchar),'-1')+'|'+isNull(cast(a.n_id as varchar),'-1') as N_ID,a.s_name from dbo.TT_RESOURCE_SUBDISTRICT a where a.S_STATE='1'";
            return broker.ExecuteQueryForOleDB(strSql, ConfigurationManager.ConnectionStrings["CRCAppConn"].ToString());
        }

    }
}
